

CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetuserDetails`(
    IN p_sort_column VARCHAR(100),
    IN p_sort_direction VARCHAR(4),
    IN p_page_number INT,
    IN p_page_size INT,
    IN p_filter_column VARCHAR(100),
    IN p_filter_value VARCHAR(100)
)
BEGIN
    -- Set default values if parameters are NULL
    SET p_sort_column = COALESCE(p_sort_column, 'user_id');
    SET p_sort_direction = COALESCE(p_sort_direction, 'ASC');
    SET p_page_number = COALESCE(p_page_number, 1);
    SET p_page_size = COALESCE(p_page_size, 10);

    -- Calculate the offset for pagination
    SET @offset = (p_page_number - 1) * p_page_size;

    -- Construct the base query for filtering
    SET @base_query = 'FROM user_details WHERE is_deleted = 0';

    -- Add filtering if filter column and value are provided
    IF p_filter_column IS NOT NULL AND p_filter_value IS NOT NULL THEN
        SET @base_query = CONCAT(@base_query, ' AND ', p_filter_column, ' LIKE CONCAT("%', p_filter_value, '%")');
    END IF;

    -- Prepare the query for total record count
    SET @total_query = CONCAT('SELECT COUNT(*) AS total_records ', @base_query);

    -- Calculate total pages
    SET @total_pages_query = CONCAT('SELECT CEIL(COUNT(*) / ', p_page_size, ') AS total_pages ', @base_query);

    -- Prepare the paginated query with total records and total pages
    SET @query = CONCAT(
        'SELECT user_id, user_first_name, user_last_name, user_email, user_status, user_role_id, is_active, is_deleted, created_on, updated_on,',
        ' (', @total_query, ') AS total_records,',
        ' (', @total_pages_query, ') AS total_pages ',
        @base_query,
        ' ORDER BY ', p_sort_column, ' ', p_sort_direction,
        ' LIMIT ', p_page_size, ' OFFSET ', @offset
    );

    -- Prepare and execute the final query
    PREPARE stmt FROM @query;
    EXECUTE stmt;

    -- Clean up
    DEALLOCATE PREPARE stmt;
END;
